<?php
class School_db extends CI_Model {

    function __construct()
    {
        parent::__construct();
    }
    
    function is_class_unique($classlabel) {
        $where = "(class_label='".$classlabel."')"; 
        $this->db->where($where);
        $result = $this->db->count_all_results('class');
        if($result>0)
            return false;
        else
            return true; 
    }
    
    function is_division_unique($classid, $divisionlabel) {
        $where = "(division_class_id=".$classid." AND division_label='".$divisionlabel."')"; 
        $this->db->where($where);
        $result = $this->db->count_all_results('division');
        //print_object($divisionlabel); exit;
        if($result>0)
            return false;
        else
            return true; 
    }
    
    function is_term_unique($termname) {
        $where = "(term_name='".$termname."')"; 
        $this->db->where($where);
        $result = $this->db->count_all_results('term');
        if($result>0)
            return false;
        else
            return true;
    }
    
    function save_class($data,$pk='') {
        $save_result = array();
        
        if ($pk=='') {
    		$this->db->insert('class', $data);
    		$save_result['insert_id'] = $this->db->insert_id();
            if($save_result['insert_id'] == 0 || $save_result['insert_id'] == false) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		} else {
    		$this->db->where('class_id', $pk);
    		$this->db->update('class', $data);
            //print_object($this->db->last_query());exit;
            $save_result['affected_rows'] = $this->db->affected_rows();
            if($save_result['affected_rows'] == -1 ) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		}   
        return $save_result;
    }
    
    function get_class_meta() {
        $meta = array(
            "table"=>"class",
            "fields" => array(
               "class_label" => array(
                    "label"=>"Class Label",
                    "type"=>"string"
               ),
               "class_agelimit" => array(
                    "label"=>"Age Limit",
                    "type"=>"string"
               )
            )
        );
        
        return $meta;
    }
    
    function get_division_meta() {
        $meta = array(
            "table"=>"division",
            "fields" => array(
                "division_label" => array(
                    "label"=>"Division",
                    "type"=>"string"
               ),
                "division_class_id" => array(
                    "label"=>"Class",
                    "type"=>"ref",
                    "ref"=>array(
                        "table"=>"class",
                        "pk"=>"class_id",
                        "value"=>"class_label"
                    )
               ),
               "division_classteacher_id" => array(
                    "label"=>"Class Teacher",
                    "type"=>"ref",
                    "ref"=>array(
                        "table"=>"staff",
                        "pk"=>"staff_id",
                        "value"=>"staff_name"
                    )
               )
            )
        );
        
        return $meta;
    }
    
    function get_division_filter_count($filterdata='') {
        $this->db->from('division dv');
        $this->db->join('class cl','dv.division_class_id = cl.class_id','left');
        $this->db->join('staff sf','dv.division_classteacher_id = sf.staff_id','left');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else
                            $this->db->where($tbl.'.'.$field,$value);
                    }
                }
            }
        }
        
        return $this->db->count_all_results();
    }
    
    function get_division_filter_list($limit=0,$start=0,$filterdata='',$include_deleted=true) {
        $this->db->from('division dv');
        $this->db->join('class cl','dv.division_class_id = cl.class_id','left');
        $this->db->join('staff sf','dv.division_classteacher_id = sf.staff_id','left');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else
                            $this->db->where($tbl.'.'.$field,$value);
                    }
                }
            }
        }
        $this->db->limit($limit, $start); 
        $query = $this->db->get();
        //print_object($this->db->last_query());
        return $query->result_array();
    }
    
    function build_division_dg_data($fiterdata='') {
        $result['dg_title'] = "Division List";
        if($this->input->post('show_all')) {
            $result['show_all'] = true;
        } else {
            //$result['show_all'] = false;
            $result['current_page'] = ($this->input->post('page')) ? $this->input->post('page') : 1;
            $result['rows_per_page'] = ($this->config->item('per_page')) ? $this->config->item('per_page') : 10;
        }
        if($fiterdata=='')
            $result['total'] = $this->get_division_filter_count();
        else
            $result['total'] = $this->get_division_filter_count($fiterdata);
            
        if($result['show_all']) {
            $result['current_page'] = 1;
            $result['rows_per_page'] = ($result['total']==0)?100:$result['total'];
        }
        
        $result['pk'] = 'division_id';
        $result['itemname'] = 'division_label';
        //print_object($fiterdata); exit;
        if(is_array($fiterdata['cols']) && count($fiterdata['cols']) > 0) {
            $dgcols = array(
                'division_id'=>array('label'=>'Division ID','type'=>'link','visible'=>true, 'href'=>'/school/viewdivision')
            );
            foreach($fiterdata['cols'] as $col) {
                $dgcol = $this->add_dg_col($col,$fiterdata['meta']['fields'][$col],$dgcols);
            }
            $result['dgcolumns'] = $dgcols;
        } else {
            $result['dgcolumns'] = array(
                'division_id'=>array('label'=>'Division ID','type'=>'link','visible'=>true, 'href'=>'/school/viewdivision'),
                'division_label'=>array('label'=>'Division','type'=>'link','visible'=>true, 'href'=>'/school/viewdivision'),
                'class_label'=>array('label'=>'Class','type'=>'string','visible'=>true),
                'staff_name'=>array('label'=>'Class Teacher','type'=>'string','visible'=>true)           
            );
        }        
        
        $result['actions'] = array(
            'edit'=>'/school/editdivision',
            'view'=>'/school/viewdivision',
            'page'=>'/school/divisionlist'
        );
        
        $offset = $result['rows_per_page']*($result['current_page']-1);
        $result['pages_total'] =  ceil($result['total']/$result['rows_per_page']);
        if($fiterdata=='')
            $result["dgitems"] = $this->get_division_filter_list($result['rows_per_page'], $offset, '');
        else
            $result["dgitems"] = $this->get_division_filter_list($result['rows_per_page'], $offset, $fiterdata);
        return $result;
    }
    
    function get_class_filter_count($filterdata='') {
        $this->db->from('class cl');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else
                            $this->db->where($tbl.'.'.$field,$value);
                    }
                }
            }
        }
        
        return $this->db->count_all_results();
    }
    
    function get_class_filter_list($limit=0,$start=0,$filterdata='',$include_deleted=true) {
        $this->db->from('class cl');
        
        if($filterdata!='') {
            $fitems = $filterdata['items'];
            foreach($fitems as $fitem) {
                $tbl = $fitem['table'];
                $field = $fitem['name'];
                $value = $fitem['value'];
                if( $value != '') {
                    if(is_array($value)) {
                        $this->db->where_in($tbl.'.'.$field,$value);
                    } else {
                        if($fitem['type']=='text')
                            $this->db->like($tbl.'.'.$field,$value);
                        else
                            $this->db->where($tbl.'.'.$field,$value);
                    }
                }
            }
        }
        
        $this->db->limit($limit, $start); 
        $query = $this->db->get();
        //print_object($this->db->last_query());
        return $query->result_array();
    }
    
    function build_class_dg_data($fiterdata='') {
        $result['dg_title'] = "Class List";
        if($this->input->post('show_all')) {
            $result['show_all'] = true;
        } else {
            //$result['show_all'] = false;
            $result['current_page'] = ($this->input->post('page')) ? $this->input->post('page') : 1;
            $result['rows_per_page'] = ($this->config->item('per_page')) ? $this->config->item('per_page') : 10;
        }
        if($fiterdata=='')
            $result['total'] = $this->get_class_filter_count();
        else
            $result['total'] = $this->get_class_filter_count($fiterdata);
            
        if($result['show_all']) {
            $result['current_page'] = 1;
            $result['rows_per_page'] = ($result['total']==0)?100:$result['total'];
        }
        
        $result['pk'] = 'class_id';
        $result['itemname'] = 'class_label';
        //print_object($fiterdata); exit;
        if(is_array($fiterdata['cols']) && count($fiterdata['cols']) > 0) {
            $dgcols = array(
                'class_id'=>array('label'=>'Class ID','type'=>'link','visible'=>true, 'href'=>'/school/viewclass')
            );
            foreach($fiterdata['cols'] as $col) {
                $dgcol = $this->add_dg_col($col,$fiterdata['meta']['fields'][$col],$dgcols);
            }
            $result['dgcolumns'] = $dgcols;
        } else {
            $result['dgcolumns'] = array(
                'class_id'=>array('label'=>'Class ID','type'=>'link','visible'=>true, 'href'=>'/school/viewclass'),
                'class_label'=>array('label'=>'Class Label','type'=>'string','visible'=>true),
                'class_agelimit'=>array('label'=>'Age Limit','type'=>'string','visible'=>true)         
            );
        }        
        
        $result['actions'] = array(
            'edit'=>'/school/editclass',
            'view'=>'/school/viewclass',
            'page'=>'/school/classlist'
        );
        
        $offset = $result['rows_per_page']*($result['current_page']-1);
        $result['pages_total'] =  ceil($result['total']/$result['rows_per_page']);
        if($fiterdata=='')
            $result["dgitems"] = $this->get_class_filter_list($result['rows_per_page'], $offset, '');
        else
            $result["dgitems"] = $this->get_class_filter_list($result['rows_per_page'], $offset, $fiterdata);
        return $result;
    }
    
    function save_division($data,$pk='') {
        $save_result = array();
        
        if ($pk=='') {
    		$this->db->insert('division', $data);
    		$save_result['insert_id'] = $this->db->insert_id();
            if($save_result['insert_id'] == 0 || $save_result['insert_id'] == false) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		} else {
    		$this->db->where('division_id', $pk);
    		$this->db->update('division', $data);
            //print_object($this->db->last_query());exit;
            $save_result['affected_rows'] = $this->db->affected_rows();
            if($save_result['affected_rows'] == -1 ) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		}   
        return $save_result;
    }
    
    function save_term($data,$pk='') {
        $save_result = array();
        
        if ($pk=='') {
    		$this->db->insert('term', $data);
    		$save_result['insert_id'] = $this->db->insert_id();
            if($save_result['insert_id'] == 0 || $save_result['insert_id'] == false) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		} else {
    		$this->db->where('term_id', $pk);
    		$this->db->update('term', $data);
            //print_object($this->db->last_query());exit;
            $save_result['affected_rows'] = $this->db->affected_rows();
            if($save_result['affected_rows'] == -1 ) {
                $save_result['result'] = 'F';
                $save_result['error_no'] = $this->db->_error_number();
                $save_result['error_message'] = $this->db->_error_message();
            } else {
                $save_result['result'] = 'S';
            }
		}   
        return $save_result;
    }
    
    function get_division_list($class_id) {
        $this->db->select('division_id, division_label');
        $query = $this->db->get_where('division', array('division_class_id'=>$class_id));
        $result = $query->result_array();
        return $result;
    }
    
    function get_class_list($get_division=false, $is_grid_data = false) {
        $query = $this->db->get('class');
        $result = $query->result_array();
        if($get_division) {
            foreach ($result as &$row) {
                $row['divisions'] = $this->get_division_list($row['class_id']);
            }
        }
        if($is_grid_data) {
            $result = format_list($result, "class_id", "class_label");
        }
        return $result;
    }
    
    function get_term_list() {
        $this->db->select('* , CONCAT( MONTHNAME( STR_TO_DATE( term_start,  "%m" ) ) ,  " to ", MONTHNAME( STR_TO_DATE( term_end,  "%m" ) ) ) AS term_label',false);
        $this->db->from('term');
        $query = $this->db->get();
        $terms = $query->result_array();
        foreach($terms as $term) {
            
        }
        return $terms;
    }
    
    function get_year_term($term, $year) {
        $term_label = "";
        $is_current_term = 0;
        $term_start = $term['term_start'];
        $term_end = $term['term_end'];
        $current_month = date("n");
        $current_year = date("Y");
        if($current_month <=3) {  // Fix for Jan & Feb where accademic year is current year - 1.
            $current_year--;
        }
        $term_start_time = mktime(10,0,0,$term_start,1,$year);
        if($term_end < $term_start) {   //Year change Eg. Nov - Feb  ie. 11 - 2
            $term_end_time = mktime(10,0,0,$term_end,1,($year+1));
            $term_label = date("M",$term_start_time)."&#8217; ".date("y",$term_start_time)." to ".date("M",$term_end_time)."&#8217; ".date("y",$term_end_time);
            if( ($current_year==$year) && (($term_start <=  $current_month && $current_month <= 12) || ( 1 <= $current_month && $current_month <= $term_end )) ) {
                $is_current_term = 1;
            }
        } else {
            $term_end_time = mktime(10,0,0,$term_end,1,$year);
            $term_label = date("M",$term_start_time)."&#8217; ".date("y",$term_start_time)." to ".date("M",$term_end_time)."&#8217; ".date("y",$term_end_time);
            if( ($current_year==$year) && $term_start <= $current_month && $current_month <= $term_end ) {
                $is_current_term = 1;
            }
        }
        $term["term_now"] = $is_current_term;
        $term["term_year"] = $year;
        $term["term_label"] = $term_label;
        return $term;
    }
    
    function get_receipt_term_list() {
        $receipt_term_list = array();
        $term_list = $this->get_term_list();
        $current_month = date("n");
        $current_year = date("Y");
        if($current_month <=7) {  // Fix for Term 1 where accademic year is current year - 1.
            $current_year--;
        }
        foreach($term_list as $term) {
            $current_year_term = $this->get_year_term($term,$current_year);
            array_push($receipt_term_list,$current_year_term);
        }
        foreach($term_list as $term) {
            $next_year_term = $this->get_year_term($term,($current_year+1));
            array_push($receipt_term_list,$next_year_term);
        }
        //print_object($receipt_term_list);exit;
        return $receipt_term_list;
    }
    
    function get_current_term() {
        $term_list = $this->get_term_list();
        $current_term_id = "";
        $current_month = date("n");
        foreach($term_list as $term) {
            $term_start = $term['term_start'];
            $term_end = $term['term_end'];
            if($term_end < $term_start) {   //Year change Eg. Nov - Feb  ie. 11 - 2
               if( ($term_start <=  $current_month && $current_month <= 12) || ( 1 <= $current_month && $current_month <= $term_end ) ) {
                    $current_term_id = $term['term_id'];
               }
            } else {
                if($term_start <= $current_month && $current_month <= $term_end ) {
                    $current_term_id = $term['term_id'];
                }
            }
        }
        return $current_term_id;
    }
    
    function get_tution_fee($classid, $termid) {
        $tution_fee = "";
        $this->db->from('tutionfee');
        $this->db->where('tf_classid', $classid);
        $this->db->where('tf_termid', $termid);
        $query = $this->db->get();
        $fees = $query->result_array();
        if(count($fees) >0) {
            $tution_fee = $fees[0]['tf_amount'];
        }
        return $tution_fee;
    }
    
    function get_transport_fee($areaid) {
        $transport_fee = "";
        $this->db->from('area');
        $this->db->where('area_id', $areaid);
        $query = $this->db->get();
        $fees = $query->result_array();
        if(count($fees) >0) {
            $transport_fee = $fees[0]['area_transport_fee'];
        }
        return $transport_fee;
    }
    
    function get_school_info() {
        $query = $this->db->get('school');
        $result = $query->result_array();
        return $result[0];
    }
    
}

?>